USE [NANS21V]
GO

/****** Object:  StoredProcedure [dbo].[PlayerSearch]    Script Date: 06/11/2014 18:18:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PlayerSearch]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[PlayerSearch]
GO

CREATE PROCEDURE [dbo].[PlayerSearch]
	@TAIKAINO int,
	@NUMBER2_1 int,
	@NUMBER2_2 int,
	@NUMBER1 varchar(20),
	@KYOGISYAMEI varchar(20),
	@KYOGISYAKANA varchar(20),
	@SYOZOKUMEI varchar(20),
	@SYOZOKUKANA varchar(20)
AS
SELECT
	AAA.NUMBER,
	AAA.NUMBER1,
	AAA.NUMBER2,
	AAA.KYOGISYAMEI,
	(dbo.Seibetu(AAA.SEIBETU)+ '<br/>' + CASE AAA.GAKUNEN WHEN '' THEN AAA.GAKUNEN ELSE '('+ AAA.GAKUNEN +')'END) AS SEIBETU,
	AAA.GAKUNEN,
	AAA.SEINEN,
	AAA.OPENFLG,
	AAA.KOJINSYOZOKUTIMEI,
	AAA.RIKUREN,
	AAA.KYOGISYANO,
	MAX(CASE num WHEN 1 THEN AAA.KYOGIMEI ELSE '' END) AS S1,
	MAX(CASE num WHEN 2 THEN AAA.KYOGIMEI ELSE '' END) AS S2,
	MAX(CASE num WHEN 3 THEN AAA.KYOGIMEI ELSE '' END) AS S3,
	MAX(CASE num WHEN 4 THEN AAA.KYOGIMEI ELSE '' END) AS S4,
	MAX(CASE num WHEN 5 THEN AAA.KYOGIMEI ELSE '' END) AS S5
FROM
	(SELECT
		KS.KYOGISYANO,
		CONVERT(varchar,KS.NUMBER1)+CONVERT(varchar,KS.NUMBER2) AS NUMBER, 	 		
		KS.NUMBER1,
		KS.NUMBER2,		
		KS.KYOGISYAKANA + '<br>' + KS.KYOGISYAMEI AS KYOGISYAMEI,
		KS.SEIBETU,
		KS.GAKUNEN,
		KS.SEINEN,
		SK.KYOGINO,
		KS.SYOZOKUNO,
		SK.KIROKUKUBUN,
		SK.OPENFLG,
		KS.KOJINSYOZOKUTIMEI,
		KS.RIKUREN,
		SK.KIROKUFLG,
		(SK.KYOGIMEI + '<br/>' + (CASE WHEN SK.SYUMOKUKUBUN = 8 THEN LEFT(SK.KIROKU,(charindex('.', SK.KIROKU)-1)) ELSE(CASE WHEN SK.KIROKU IS NULL THEN '' ELSE 
		dbo.Kiroku(SK.KIROKU, SK.SYUMOKUKUBUN,SK.KIROKUKUBUN,SK.KIROKUFLG)END)END)) AS KYOGIMEI,--20140514
		ROW_NUMBER() OVER(PARTITION BY KS.KYOGISYANO ORDER BY KYOGISYAMEI) AS num
	FROM  
		(SELECT
			WKS.KYOGISYANO,
			WKS.NUMBER1,
			WKS.NUMBER2,
			WKS.SEINEN,
			WKS.KYOGISYAMEI,
			WKS.KYOGISYAKANA,
			WKS.KOJINSYOZOKUTIMEI,
			WKS.RIKUREN,
			WKS.SEIBETU,
			WKS.GAKUNEN ,
			WKS.SYOZOKUNO1 AS SYOZOKUNO,
			WS.SYOZOKUMEI,
			WS.SYOZOKUKANA,
			WKS.KYOGISYAFLG 
		FROM 
			TBL_KYOGISYA AS WKS ,
			TBL_SYOZOKU AS WS 
		WHERE
			WS.SYOZOKUNO = WKS.SYOZOKUNO1
			AND WKS.TAIKAINO = @TAIKAINO
			AND WKS.TAIKAINO = WS.TAIKAINO
		UNION 
		SELECT
			WKS.KYOGISYANO,
			WKS.NUMBER1,
			WKS.NUMBER2,
			WKS.SEINEN,
			WKS.KYOGISYAMEI,
			WKS.KYOGISYAKANA,
			WKS.KOJINSYOZOKUTIMEI,
			WKS.RIKUREN,
			WKS.SEIBETU,
			WKS.GAKUNEN ,
			WKS.SYOZOKUNO2 AS SYOZOKUNO,
			WS.SYOZOKUMEI,
			WS.SYOZOKUKANA,
			WKS.KYOGISYAFLG 
		FROM
			TBL_KYOGISYA AS WKS,
			TBL_SYOZOKU AS WS 
		WHERE
			WS.SYOZOKUNO = WKS.SYOZOKUNO2 
			AND WKS.TAIKAINO = @TAIKAINO 
			AND WKS.TAIKAINO = WS.TAIKAINO
	) AS KS 
	LEFT OUTER JOIN 
	(SELECT
		WK.KYOGINO,
		SM.SYUMOKUKUBUN,
		WK.KYOGIMEI,
		WSK.KYOGISYANO,
		WSK.KIROKU,
		SM.KIROKUKUBUN,
		WSK.OPENFLG ,
		WSK.KIROKUFLG
	FROM
		TBL_SANKAKYOGI AS WSK,
		TBL_KYOGI AS WK,
		TBL_SYUMOKU_MASTER AS SM 
	WHERE
		WSK.TAIKAINO = WK.TAIKAINO 
		AND WK.TAIKAINO = @TAIKAINO 
		AND WSK.KYOGINO = WK.KYOGINO 
		AND WK.GATTAIFLG <> 2 
		AND WK.SYUMOKUNO = SM.SYUMOKUNO
) AS SK 
ON KS.KYOGISYANO = SK.KYOGISYANO 
WHERE
	(KS.KYOGISYAFLG = 0 OR KS.KYOGISYAFLG IS NULL) 
	AND KS.NUMBER1 LIKE @NUMBER1 
	AND KS.NUMBER2 BETWEEN @NUMBER2_1 AND CASE WHEN @NUMBER2_2 = -1 THEN KS.NUMBER2 ELSE @NUMBER2_2 END
	AND (KS.KYOGISYAMEI LIKE @KYOGISYAMEI OR KS.KYOGISYAKANA LIKE @KYOGISYAKANA) 
	AND (KS.SYOZOKUMEI LIKE @SYOZOKUMEI OR KS.SYOZOKUKANA LIKE @SYOZOKUKANA)
)AS AAA 
GROUP BY 
	AAA.NUMBER,
	AAA.NUMBER1,
	AAA.NUMBER2,
	AAA.KYOGISYAMEI,
	AAA.SEIBETU,
	AAA.GAKUNEN,
	AAA.SEINEN,
	AAA.OPENFLG,
	AAA.KYOGISYANO,
	AAA.KOJINSYOZOKUTIMEI,
	AAA.RIKUREN
ORDER BY
	AAA.NUMBER1,
	AAA.NUMBER2,
	AAA.SEIBETU
GO

